import json
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref
from datetime import datetime


app = Flask(__name__, template_folder="templates", static_folder="static")
# 连接数据库连接url
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://zhangyz:123456@10.4.7.13:3306/school?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
# 查询时会显示原始SQL语句
app.config["SQLALCHEMY_ECHO"] = True

# 把SQLAlchemy组件注册到项目中
db = SQLAlchemy()
db.init_app(app)


class StudentCourse(db.Model):
    __tablename__ = "t_vf_nvm_student_course"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    sid = db.Column(db.Integer, index=True, comment="学生ID")
    cid = db.Column(db.Integer, index=True, comment="课程ID")
    create_time = db.Column(db.DateTime, default=datetime.now, comment="创建时间")

    # 关联属性 虚拟外键
    student = db.relationship("Student", uselist=False, backref=backref("to_relation", uselist=True, lazy="dynamic"),
        primaryjoin="Student.id==StudentCourse.sid",
        foreign_keys="StudentCourse.sid"
    )

    course = db.relationship("Course", uselist=False, backref=backref("to_relation", uselist=True, lazy="dynamic"),
        primaryjoin="Course.id==StudentCourse.cid",
        foreign_keys="StudentCourse.cid"
    )


class Student(db.Model):
    """学生信息模型"""
    __tablename__ = "t_vf_nvm_student"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    name = db.Column(db.String(15), index=True, comment="姓名")
    age = db.Column(db.SmallInteger, comment="年龄")
    sex = db.Column(db.Boolean, default=True, comment="性别")
    money = db.Column(db.Numeric(10, 2), default=0.0, comment="钱包")

    def __repr__(self):
        return f"<{self.name} {self.__class__.__name__}>"

class Course(db.Model):
    """课程信息模型"""
    __tablename__ = "t_vf_nvm_course"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    name = db.Column(db.String(255), unique=True, comment="课程名称")
    price = db.Column(db.Numeric(10, 2), default=0.0, comment="价格")

    def __repr__(self):
        return f"<{self.name} {self.__class__.__name__}>"


@app.route("/add_data01")
def add_data01():
    # 添加测试数据
    stu01 = Student(name="Jack-01", age=19, sex=True, money=10000)
    stu02 = Student(name="Jack-02", age=19, sex=True, money=20000)
    stu03 = Student(name="Jack-03", age=19, sex=True, money=20000)
    stu04 = Student(name="Jack-04", age=19, sex=True, money=20000)
    db.session.add_all([stu01, stu02, stu03, stu04])
    db.session.commit()

    course1 = Course(name="Python基础第1季", price=1000)
    course2 = Course(name="Python基础第2季", price=1000)
    course3 = Course(name="Python基础第3季", price=1000)
    course4 = Course(name="Python基础第4季", price=1000)
    course5 = Course(name="Python基础第5季", price=1000)
    db.session.add_all([course1, course2, course3, course4, course5])
    db.session.commit()

    return "ok"

@app.route("/add_data02")
def add_data02():
    # 学生购买课程
    data = [
        StudentCourse(sid=1, cid=1),
        StudentCourse(sid=1, cid=2),
        StudentCourse(sid=1, cid=3),
        StudentCourse(sid=2, cid=1),
        StudentCourse(sid=2, cid=2),
        StudentCourse(sid=3, cid=3),
        StudentCourse(sid=3, cid=4),
        StudentCourse(sid=4, cid=1),
        StudentCourse(sid=4, cid=2),
        StudentCourse(sid=4, cid=5),
        StudentCourse(sid=5, cid=1),
        StudentCourse(sid=5, cid=2),
        StudentCourse(sid=5, cid=3),
        StudentCourse(sid=5, cid=4),
    ]
    db.session.add_all(data)
    db.session.commit()

    return "ok"

@app.route("/query_data01")
def query_data01():
    """查询3号学生购买了哪些课程？"""
    student = Student.query.get(3)
    print([{"id": relation.course.id, "name": relation.course.name} for relation in student.to_relation.all()])
    return "ok"


if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(host='0.0.0.0', port=5000, debug=True)